﻿// <copyright>
// Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web.Mvc;
using CloudMLDemo.Models;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;
using Newtonsoft.Json;

namespace CloudMLDemo.Controllers
{
    public class HomeController : Controller
    {
        private const int PreviewRows = 10;

        public DataDisplayTable DataTable = new DataDisplayTable();

        public List<string> RrsInputParameters;

        public List<string> RrsOutputParameters;

        public string WebServiceEndPoint;

        public string WebServiceKey;

        public bool IsSuccessful;

        private const string StorageAccountName = @"[your storage account name]";

        private const string StorageAccountKey = @"[your storage account key]";

        private readonly Uri _storageBaseUrl = new Uri("https://" + StorageAccountName + ".blob.core.windows.net");

        //
        // GET: /Home/

        public ActionResult Index()
        {
            return View();
        }

        //
        // POST: /Home/Validate

        [HttpPost]
        public ActionResult Validate(FormCollection collection)
        {
            var view = View();
            var sqlConnectionString = collection["SqlConnectionString"];
            view.ViewData["SqlConnectionString"] = sqlConnectionString;
            var query = collection["Query"];
            view.ViewData["Query"] = query;
            WebServiceEndPoint = collection["WSEndpoint"];
            WebServiceKey = collection["WSKey"];
            IsSuccessful = true;
            // Validate that schema from SQL matches that of RRS
            try
            {
                var metadata = DownloadModelMetaData(WebServiceEndPoint);
                RrsInputParameters = GetModelInputNames(metadata).ToList();
            }
            catch (Exception e)
            {

                view.ViewData["ValidationMessage"] = "RRS parse failed:" + e.GetBaseException();
                return view;
            }
            try
            {
                using (var conn = new SqlConnection(sqlConnectionString))
                {
                    using (SqlCommand command = conn.CreateCommand())
                    {
                        conn.Open();
                        command.CommandText = query;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            int count = 0;

                            var schema = reader.GetSchemaTable();
                            var columnsList = GetColumnsList(schema).ToList();
                            if (!RrsInputParameters.SequenceEqual(columnsList))
                            {
                                view.ViewData["ValidationMessage"] =
                                    "Returned SQL schema does not meet RRS input metadata";
                                IsSuccessful = false;
                            }

                            DataTable.ColumnNames = columnsList;

                            // Loop over the results, get first 10 rows
                            while (reader.Read() && count < PreviewRows)
                            {
                                var row = new List<string>();
                                for (int i = 0; i < columnsList.Count; i++)
                                {
                                    row.Add(reader[i].ToString());
                                }
                                DataTable.Rows.Add(row);
                                count++;
                            }

                            conn.Close();
                            if (IsSuccessful)
                            {
                                view.ViewData["ValidationMessage"] =
                                    "Data schema matches, please proceed!";
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                view.ViewData["ValidationMessage"] = "Sql query failed:" + e.GetBaseException();
                return view;
            }

            return view;
        }

        //
        // POST: /Home/Preview
        // Display scoring result for first 10 lines
        [HttpPost]
        public ActionResult Preview(FormCollection collection)
        {
            var view = View();
            var sqlConnectionString = collection["SqlConnectionString"];
            view.ViewData["SqlConnectionString"] = sqlConnectionString;
            var query = collection["Query"];
            view.ViewData["Query"] = query;
            WebServiceEndPoint = collection["WSEndpoint"];
            WebServiceKey = collection["WSKey"];
            var metadata = DownloadModelMetaData(WebServiceEndPoint);
            RrsInputParameters = GetModelInputNames(metadata).ToList();
            RrsOutputParameters = GetModelOutputNames(metadata).ToList();
            DataTable.ColumnNames = RrsOutputParameters.ToList();
            using (var conn = new SqlConnection(sqlConnectionString))
            {
                using (SqlCommand command = conn.CreateCommand())
                {
                    conn.Open();
                    command.CommandText = query;
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        var count = 0;
                        // Loop over the results
                        while (reader.Read() && count < PreviewRows)
                        {
                            string scoreResult = GetScoreResult(reader, WebServiceKey).Result;
                            DataTable.Rows.Add(scoreResult.Trim('[', ']').Split(',').ToList());
                            count++;
                        }
                    }
                }
            }

            return view;
        }

        // POST: /Home/RunJob
        [HttpPost]
        public async Task<ActionResult> RunJob(FormCollection collection)
        {
            var view = View();
            var sqlConnectionString = collection["SqlConnectionString"];
            view.ViewData["SqlConnectionString"] = sqlConnectionString;
            var query = collection["Query"];
            view.ViewData["Query"] = query;
            WebServiceEndPoint = collection["WSEndpoint"];
            WebServiceKey = collection["WSKey"];
            var metadata = DownloadModelMetaData(WebServiceEndPoint);
            RrsInputParameters = GetModelInputNames(metadata).ToList();
            RrsOutputParameters = GetModelOutputNames(metadata).ToList();
            var blobClient = new CloudBlobClient(_storageBaseUrl,
                new StorageCredentials(StorageAccountName, StorageAccountKey));
            CloudBlobContainer container = blobClient.GetContainerReference("demojobs");
            container.CreateIfNotExists();
            container.SetPermissions(new BlobContainerPermissions { PublicAccess = BlobContainerPublicAccessType.Blob });
            
            // Pull data from SQL and write into blob
            var inputBlobName = Guid.NewGuid() + ".arff";
            CloudBlockBlob inputBlob = container.GetBlockBlobReference(inputBlobName);
            using (var blobStream = inputBlob.OpenWrite())
            {
                var inputStream = new StreamWriter(blobStream);
                inputStream.WriteLine("@RELATION	Unnamed");
                RrsInputParameters.ForEach(
                    param => inputStream.WriteLine("@ATTRIBUTE	" + param + "	NUMERIC"));

                inputStream.WriteLine("@DATA");
                using (var conn = new SqlConnection(sqlConnectionString))
                {
                    using (SqlCommand command = conn.CreateCommand())
                    {
                        conn.Open();
                        command.CommandText = query;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // Loop over the results
                            while (reader.Read())
                            {
                                List<object> sqlEntry = GetRow(reader).ToList();
                                inputStream.WriteLine(string.Join(",", sqlEntry));
                            }
                        }
                    }
                }

                inputStream.Flush();
            }

            IsSuccessful = true;
            // Submit the job
            var input = new AzureBlobDataReference
            {
                ConnectionString = "DefaultEndpointsProtocol=https;AccountName=" + StorageAccountName + ";AccountKey=" + StorageAccountKey,
                RelativeLocation = container.Name + "/" + inputBlobName
            };

            var output = new AzureBlobDataReference();

            var scoreAddress = WebServiceEndPoint.Replace("/odata/workspaces/", "/workspaces/").TrimEnd('/') + "/jobs";
            using (var client = new HttpClient())
            {
                client.DefaultRequestHeaders.Accept.Clear();
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", WebServiceKey);
                var response = await client.PostAsJsonAsync(scoreAddress, new BatchScoreRequest { Input = input, Output = output }).ConfigureAwait(false);
                if (response.IsSuccessStatusCode)
                {
                    var job = await response.Content.ReadAsStringAsync().ConfigureAwait(false);
                    var jobId = job.Trim('"');
                    view.ViewData["jobAddress"] = scoreAddress + "/" + jobId;
                }
                else
                {
                    IsSuccessful = false;
                }
                
            }

            return view;
        }

        // POST: /Home/DownloadJob
        [HttpPost]
        public async Task<ActionResult> DownloadJob(FormCollection collection)
        {
            var view = View();
            var jobAddress = collection["jobAddress"];
            WebServiceKey = collection["WSKey"];
            IsSuccessful = true;
            using (var client = new WebClient())
            {
                client.Headers.Add("Authorization", "Bearer " + WebServiceKey);
                var jsonString = client.DownloadString(jobAddress);
                BatchScoreStatus status = await JsonConvert.DeserializeObjectAsync<BatchScoreStatus>(jsonString);
                if (status.StatusCode != BatchScoreStatusCode.Finished)
                {
                    IsSuccessful = false;
                }
                else
                {
                    view.ViewData["downloadAddress"] = status.Result.BaseLocation + status.Result.RelativeLocation +
                                          status.Result.SasBlobToken;
                }
            }

            return view;

        }

        // POST: /Home/CheckJob
        [HttpPost]
        public ActionResult CheckJob(FormCollection collection)
        {
            var view = View();
            var jobAddress = collection["jobAddress"];
            WebServiceKey = collection["WSKey"];
            using (var client = new WebClient())
            {
                client.Headers.Add("Authorization", "Bearer " + WebServiceKey);
                var jsonString = client.DownloadString(jobAddress);
                view.ViewData["JobStatus"] = jsonString;
            }
            return view;
        }


        [HttpPost]
        public async Task<ActionResult> DumpJob(FormCollection collection)
        {
            var view = View();
            var sqlConnectionString = collection["SqlConnectionString"];
            view.ViewData["SqlConnectionString"] = sqlConnectionString;
            var jobAddress = collection["jobAddress"];
            var tableName = collection["tableName"];
            WebServiceEndPoint = collection["WSEndpoint"];
            WebServiceKey = collection["WSKey"];
            var metadata = DownloadModelMetaData(WebServiceEndPoint);
            RrsOutputParameters = GetModelOutputNames(metadata).ToList();
            view.ViewData["tableName"] = tableName;
            string blobAddress;

            // download RRS response, which contains the job status
            using (var client = new WebClient())
            {
                client.Headers.Add("Authorization", "Bearer " + WebServiceKey);
                var jsonString = client.DownloadString(jobAddress);
                BatchScoreStatus status = await JsonConvert.DeserializeObjectAsync<BatchScoreStatus>(jsonString);

                blobAddress = status.Result.BaseLocation + status.Result.RelativeLocation +
                                          status.Result.SasBlobToken;
            }

            // download job output from blob storage
            string outputData;
            using (var client = new WebClient())
            {
                outputData = client.DownloadString(new Uri(blobAddress));
            }

            // add it to database
            using (var conn = new SqlConnection(sqlConnectionString))
            {
                using (SqlCommand command = conn.CreateCommand())
                {
                    conn.Open();
                    var createCommand = new StringBuilder();
                    createCommand.Append("CREATE TABLE " + tableName + "("
                                         + "[Id] [int] IDENTITY(1,1) NOT NULL,");
                    foreach (var parameter in RrsOutputParameters)
                    {
                        createCommand.Append(parameter.Replace(" ", string.Empty) + " [real] NULL,");
                    }
                    createCommand.Append(" PRIMARY KEY CLUSTERED ([Id] ASC))");

                    command.CommandText = createCommand.ToString();
                    command.ExecuteNonQuery();
                }

                using (SqlCommand command = conn.CreateCommand())
                {
                    var createCommand = new StringBuilder();
                    createCommand.Append("INSERT INTO " + tableName + "(");
                    createCommand.Append(string.Join(",", RrsOutputParameters.Select(p => p.Replace(" ", string.Empty))));
                    createCommand.Append(") VALUES");
                    foreach (var entry in
                            outputData.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Skip(1))
                    {
                        createCommand.Append("(" + string.Join(",", entry.Split(',')) + "),");
                    }
                    command.CommandText = createCommand.ToString().TrimEnd(',');
                    command.ExecuteNonQuery();
                }
            }
            return view;
        }

        private IEnumerable<object> GetRow(SqlDataReader reader)
        {
            for (var i = 0; i < RrsInputParameters.Count; i++)
            {
                yield return reader[i];
            }
        }

        private async Task<string> GetScoreResult(SqlDataReader reader, string wsKey)
        {
            var request = new ScoreRequest
            {
                Id = Guid.NewGuid().ToString("N"),
                Instance = new ScoreData
                {
                    FeatureVector = new Dictionary<string, string>()
                }
            };

            for (int i = 0; i < RrsInputParameters.Count; i++)
            {
                request.Instance.FeatureVector.Add(RrsInputParameters[i], reader[i].ToString());
            }

            var scoreAddress = WebServiceEndPoint.Replace("/odata/workspaces/", "/workspaces/").TrimEnd('/') + "/score";
            using (var client = new HttpClient())
            {
                client.DefaultRequestHeaders.Accept.Clear();
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                client.DefaultRequestHeaders.Add("Authorization", "Bearer " + wsKey);
                var response = await client.PostAsJsonAsync(scoreAddress, request).ConfigureAwait(false);
                return await response.Content.ReadAsStringAsync().ConfigureAwait(false);
            }
        }

        private IEnumerable<string> GetColumnsList(DataTable schema)
        {
            return from DataRow field in schema.Rows select field["ColumnName"] as string;
        }

        private string DownloadModelMetaData(string endpoint)
        {
            string metadataPath = endpoint.TrimEnd('/') + "/$metadata";

            using (var client = new WebClient())
            {
                return client.DownloadString(new Uri(metadataPath));
            }
        }


        private IEnumerable<string> GetModelInputNames(string metadata)
        {
            var regex = new Regex(@"<Parameter Name=.*");
            var matches = regex.Matches(metadata);
            return from object match in matches select match.ToString().Split('"')[1];
        }

        private IEnumerable<string> GetModelOutputNames(string metadata)
        {
            var regex = new Regex(@"<Property Name=.*");
            var matches = regex.Matches(metadata);
            return from object match in matches select match.ToString().Split('"')[1];
        }
    }
}
